package com.slx.sthq.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelUtils {
    public static void readExcel(String fileName) throws IOException {
        // 创建文件输入流
        FileInputStream fileInputStream = new FileInputStream(new File(fileName));
        // 创建工作簿对象
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        StringBuffer resBuffer = new StringBuffer();

        // 遍历工作表中的所有行和单元格
        for (Row row : sheet) {
            for (Cell cell : row) {
                // 根据单元格的类型读取数据
                switch (cell.getCellType()) {
                    case STRING:
                        //System.out.print(cell.getStringCellValue() + "\t");
                        resBuffer.append(cell.getStringCellValue() + "\t");
                        break;
                    case NUMERIC:
                        //System.out.print(cell.getNumericCellValue() + "\t");
                        resBuffer.append(cell.getNumericCellValue() + "\t");
                        break;
                    case BOOLEAN:
                        // System.out.print(cell.getBooleanCellValue() + "\t");
                        resBuffer.append(cell.getBooleanCellValue() + "\t");
                        break;
                    case FORMULA:
                        // System.out.print(cell.getCellFormula() + "\t");
                        resBuffer.append(cell.getCellFormula() + "\t");
                        break;
                    case BLANK:
                        System.out.print("\t");
                        break;
                    default:
                        System.out.print("\t");
                }
            }
            System.out.println();
        }
        // 关闭文件输入流
        fileInputStream.close();
    }

    public static void writeExcel(String fileName) throws IOException {
        // 创建工作簿对象
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        // 创建行和单元格
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Hello, World!");
        // 设置单元格样式
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        cell.setCellStyle(style);
        // 导出 Excel 文件
        FileOutputStream fileOut = new FileOutputStream(fileName);
        workbook.write(fileOut);
        fileOut.close();
    }

    public static String readTextExcelByPath(String path) throws IOException {
        // 创建文件输入流
        FileInputStream fileInputStream = new FileInputStream(new File(path));
        // 创建工作簿对象
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        StringBuffer resBuffer = new StringBuffer();

        // 遍历工作表中的所有行和单元格
        for (Row row : sheet) {
            for (Cell cell : row) {
                // 根据单元格的类型读取数据
                switch (cell.getCellType()) {
                    case STRING:
                        //System.out.print(cell.getStringCellValue() + "\t");
                        resBuffer.append(cell.getStringCellValue() + "\t");
                        break;
                    case NUMERIC:
                        //System.out.print(cell.getNumericCellValue() + "\t");
                        resBuffer.append(cell.getNumericCellValue() + "\t");
                        break;
                    case BOOLEAN:
                        // System.out.print(cell.getBooleanCellValue() + "\t");
                        resBuffer.append(cell.getBooleanCellValue() + "\t");
                        break;
                    case FORMULA:
                        // System.out.print(cell.getCellFormula() + "\t");
                        resBuffer.append(cell.getCellFormula() + "\t");
                        break;
                    case BLANK:
                        // resBuffer.append("\t");
                        // System.out.print("\t");
                        break;
                    default:
                        // resBuffer.append("\t");
                        // System.out.print("\t");
                }
            }
            // System.out.println();
        }
        // 关闭文件输入流
        fileInputStream.close();
        return resBuffer.toString();
    }

    public static String readTextExcelByFile(File file) throws IOException {
        // 创建文件输入流
        FileInputStream fileInputStream = new FileInputStream(file);
        // 创建工作簿对象
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        StringBuffer resBuffer = new StringBuffer();

        // 遍历工作表中的所有行和单元格
        for (Row row : sheet) {
            for (Cell cell : row) {
                // 根据单元格的类型读取数据
                switch (cell.getCellType()) {
                    case STRING:
                        //System.out.print(cell.getStringCellValue() + "\t");
                        resBuffer.append(cell.getStringCellValue() + "\t");
                        break;
                    case NUMERIC:
                        //System.out.print(cell.getNumericCellValue() + "\t");
                        resBuffer.append(cell.getNumericCellValue() + "\t");
                        break;
                    case BOOLEAN:
                        // System.out.print(cell.getBooleanCellValue() + "\t");
                        resBuffer.append(cell.getBooleanCellValue() + "\t");
                        break;
                    case FORMULA:
                        // System.out.print(cell.getCellFormula() + "\t");
                        resBuffer.append(cell.getCellFormula() + "\t");
                        break;
                    case BLANK:
                        // resBuffer.append("\t");
                        // System.out.print("\t");
                        break;
                    default:
                        // resBuffer.append("\t");
                        // System.out.print("\t");
                }
            }
            // System.out.println();
        }
        // 关闭文件输入流
        fileInputStream.close();
        return resBuffer.toString();
    }

    public static void main(String[] args) throws IOException {
        String fileName = "C:\\Users\\admin\\Desktop\\jar\\需要做的功能.xlsx";
        // 读取 Excel 文件
        String s = ExcelUtils.readTextExcelByPath(fileName);

        System.out.println(s);
//        // 导出 Excel 文件
//        ExcelUtils.writeExcel(fileName);
    }
}
